package com.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.List;

import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.bean.SessionBean;
import com.bean.ThreeTuple;
import com.bean.TwoTuple;
import com.dao.ISessionDao;

public class SessionDao extends SimpleJdbcDaoSupport implements ISessionDao {
	
	//失败返回0 成功返回sessionID
	public int insertTopic(final SessionBean bean){
		final String sql = "insert into tb_session(topic,ename,cname,public,staff_unread,director_unread) values(?,?,?,?,?,?)";
		KeyHolder holder = new GeneratedKeyHolder();
		getJdbcTemplate().update(new PreparedStatementCreator(){
			public PreparedStatement createPreparedStatement(Connection conn)
					throws SQLException {
				int i = 0;
				PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				ps.setString(++i, bean.getTopic());
				ps.setString(++i, bean.getEname());
				ps.setString(++i, bean.getCname());
				ps.setString(++i, bean.getIsPublic());
				ps.setString(++i, bean.getIsReadS());
				ps.setString(++i, bean.getIsReadD());
				return ps;
			}
		}, holder);
		return holder.getKey().intValue();
	}
	
	//值班员获取与自己相关的会话信息
	public TwoTuple<Integer, List<SessionBean>> getSessionByStaff(String ename, int offset, int rows){
		//TwoTuple<List<SessionBean>, List<SessionBean>> tuple = null;
		TwoTuple<Integer, List<SessionBean>> tuple = null;
		final int total;
		String sql_total = "select count(*) from tb_session where ename=?";
		total = getSimpleJdbcTemplate().queryForInt(sql_total, ename);
		if(total == 0){
			return new TwoTuple<Integer, List<SessionBean>>(0, null);
		}
		
		final SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String sql = "select * from tb_session where ename=? order by staff_unread desc, joinTime desc limit ?,?";
		List<SessionBean> session_lst = getSimpleJdbcTemplate().query(sql, new ParameterizedRowMapper<SessionBean>(){
											public SessionBean mapRow(ResultSet rs, int index)
													throws SQLException {
												SessionBean bean = new SessionBean();
												bean.setId(rs.getInt("id"));
												bean.setTopic(rs.getString("topic"));
												bean.setEname(rs.getString("ename"));
												bean.setCname(rs.getString("cname"));
												bean.setIsPublic(rs.getString("public"));
												bean.setIsReadS(rs.getString("staff_unread"));
												bean.setIsReadD(rs.getString("director_unread"));
												bean.setJoinTime(format.format(rs.getTimestamp("joinTime")));
												return bean;
											}
										}, ename,offset,rows);
		tuple = new TwoTuple<Integer, List<SessionBean>>(total, session_lst);
		
		
//		String sql_read = "select * from tb_session where staff_unread=0 and ename=? order by joinTime desc limit ?,? order by joinTime desc";
//		List<SessionBean> read_lst = getSimpleJdbcTemplate().query(sql_read, new ParameterizedRowMapper<SessionBean>(){
//										public SessionBean mapRow(ResultSet rs, int index)
//												throws SQLException {
//											SessionBean bean = new SessionBean();
//											bean.setId(rs.getInt("id"));
//											bean.setTopic(rs.getString("topic"));
//											bean.setEname(rs.getString("ename"));
//											bean.setCname(rs.getString("cname"));
//											bean.setIsPublic(rs.getString("public"));
//											bean.setIsReadS(rs.getString("staff_unread"));
//											bean.setIsReadD(rs.getString("director_unread"));
//											bean.setJoinTime(format.format(rs.getTimestamp("joinTime")));
//											return bean;
//										}
//									}, ename, offset, rows);
//		tuple = new TwoTuple<List<SessionBean>, List<SessionBean>>(unread_lst, read_lst);
		return tuple;
	}
	
	//主任获取与自己相关的会话信息
	public TwoTuple<Integer, List<SessionBean>> getSessionByDirector(int offset, int rows){
		TwoTuple<Integer, List<SessionBean>> tuple = null;
		final int total;
		String sql_total = "select count(*) from tb_session";
		total = getSimpleJdbcTemplate().queryForInt(sql_total);
		if(total == 0){
			return new TwoTuple<Integer, List<SessionBean>>(0, null);
		}
		final SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String sql = "select * from tb_session order by director_unread desc, joinTime desc limit ?,?";
		List<SessionBean> session_lst = getSimpleJdbcTemplate().query(sql, new ParameterizedRowMapper<SessionBean>(){
											public SessionBean mapRow(ResultSet rs, int index)
													throws SQLException {
												SessionBean bean = new SessionBean();
												bean.setId(rs.getInt("id"));
												bean.setTopic(rs.getString("topic"));
												bean.setEname(rs.getString("ename"));
												bean.setCname(rs.getString("cname"));
												bean.setIsPublic(rs.getString("public"));
												bean.setIsReadS(rs.getString("staff_unread"));
												bean.setIsReadD(rs.getString("director_unread"));
												bean.setJoinTime(format.format(rs.getTimestamp("joinTime")));
												return bean;
											}
										},offset, rows);
		
		tuple = new TwoTuple<Integer, List<SessionBean>>(total, session_lst);
		return tuple;
	}
	
	
	
	//主任获取与自己相关的会话信息
	@Deprecated
	public TwoTuple<List<SessionBean>, List<SessionBean>> getSessionByDirector(){
		TwoTuple<List<SessionBean>, List<SessionBean>> tuple = null;
		final SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String sql_unread = "select * from tb_session where director_unread=1 order by joinTime desc";
		List<SessionBean> unread_lst = getSimpleJdbcTemplate().query(sql_unread, new ParameterizedRowMapper<SessionBean>(){
											public SessionBean mapRow(ResultSet rs, int index)
													throws SQLException {
												SessionBean bean = new SessionBean();
												bean.setId(rs.getInt("id"));
												bean.setTopic(rs.getString("topic"));
												bean.setEname(rs.getString("ename"));
												bean.setCname(rs.getString("cname"));
												bean.setIsPublic(rs.getString("public"));
												bean.setIsReadS(rs.getString("staff_unread"));
												bean.setIsReadD(rs.getString("director_unread"));
												bean.setJoinTime(format.format(rs.getTimestamp("joinTime")));
												return bean;
											}
										});
		String sql_read = "select * from tb_session where director_unread=0 order by joinTime desc";
		List<SessionBean> read_lst = getSimpleJdbcTemplate().query(sql_read, new ParameterizedRowMapper<SessionBean>(){
										public SessionBean mapRow(ResultSet rs, int index)
												throws SQLException {
											SessionBean bean = new SessionBean();
											bean.setId(rs.getInt("id"));
											bean.setTopic(rs.getString("topic"));
											bean.setEname(rs.getString("ename"));
											bean.setCname(rs.getString("cname"));
											bean.setIsPublic(rs.getString("public"));
											bean.setIsReadS(rs.getString("staff_unread"));
											bean.setIsReadD(rs.getString("director_unread"));
											bean.setJoinTime(format.format(rs.getTimestamp("joinTime")));
											return bean;
										}
									});
		tuple = new TwoTuple<List<SessionBean>, List<SessionBean>>(unread_lst, read_lst);
		return tuple;
	}
	
	//获取可公开的会话信息
	public TwoTuple<Integer,List<SessionBean>> getPublicSession(int offset, int rows){
		TwoTuple<Integer, List<SessionBean>> tuple = null;
		final SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		int total;
		String sql_total = "select count(*) from tb_session where public = 1";
		total = getSimpleJdbcTemplate().queryForInt(sql_total);
		if(total == 0){
			return new TwoTuple<Integer, List<SessionBean>>(0, null);
		}
		String sql_unread = "select * from tb_session where public = 1 order by joinTime desc limit ?,?";
		List<SessionBean> list = getSimpleJdbcTemplate().query(sql_unread, new ParameterizedRowMapper<SessionBean>(){
									public SessionBean mapRow(ResultSet rs, int index)
											throws SQLException {
										SessionBean bean = new SessionBean();
										bean.setId(rs.getInt("id"));
										bean.setTopic(rs.getString("topic"));
										bean.setEname(rs.getString("ename"));
										bean.setCname(rs.getString("cname"));
										bean.setIsPublic(rs.getString("public"));
										bean.setIsReadS(rs.getString("staff_unread"));
										bean.setIsReadD(rs.getString("director_unread"));
										bean.setJoinTime(format.format(rs.getTimestamp("joinTime")));
										return bean;
									}
								},offset,rows);
		tuple = new TwoTuple<Integer, List<SessionBean>>(total, list);
		return tuple;
	}
	
	//获取可公开的会话信息
//	public List<SessionBean> getPublicSession(){
//		final SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//		String sql_unread = "select * from tb_session where public = 1 order by joinTime desc";
//		return 
//			getSimpleJdbcTemplate().query(sql_unread, new ParameterizedRowMapper<SessionBean>(){
//				public SessionBean mapRow(ResultSet rs, int index)
//						throws SQLException {
//					SessionBean bean = new SessionBean();
//					bean.setId(rs.getInt("id"));
//					bean.setTopic(rs.getString("topic"));
//					bean.setEname(rs.getString("ename"));
//					bean.setCname(rs.getString("cname"));
//					bean.setIsPublic(rs.getString("public"));
//					bean.setIsReadS(rs.getString("staff_unread"));
//					bean.setIsReadD(rs.getString("director_unread"));
//					bean.setJoinTime(format.format(rs.getTimestamp("joinTime")));
//					return bean;
//				}
//			});
//		
//	}
	
	//更改值班员未读状态
	public int updateStaffUnread(int id, int unread){
		int ret = 0;
		String sql = "update tb_session set staff_unread=? where id=?";
		ret = getSimpleJdbcTemplate().update(sql, unread, id);
		return ret;
	}
	
	//更改主任未读状态
	public int updateDirectorUnread(int id, int unread){
		int ret = 0;
		String sql = "update tb_session set director_unread=? where id=?";
		ret = getSimpleJdbcTemplate().update(sql, unread, id);
		return ret;
	}
	
	public int delTopic(int id){
		int ret = 0;
		String sql = "delete from tb_session where id=?";
		ret = getSimpleJdbcTemplate().update(sql, id);
		return ret;
	}
}
